<?php

class CRTBlock {

	const TableName = BLOCKS_TBL;
	const LogTableName = LOG_TBL;

	public function __construct($id) {
		$id = Clean::posInt($id);
		$this->init($id);
	}

	private function init($id) {
		$db = DBs::getGISDB();
		$id = Clean::posInt($id);
	
		$this->db = $db;

		$sql = "SELECT * FROM ".CRTBlock::TableName." WHERE oid=$id";
		$res = $db->GetRow($sql);

		if($res) {
			foreach($res as $k => $r) {
				$this->$k = $r;
			}
		}
	}

	public function saveNH($nhName) {
		$nhName = Clean::string($nhName);
	
		$this->logUpdate($nhName);
		$sql = "UPDATE ".CRTBlock::TableName." SET name1=".$this->db->qstr($nhName)." WHERE oid=".$this->oid;
		$this->db->_Execute($sql);
		
	}

	public function logUpdate($nhName) {
		$nhName = Clean::string($nhName, false, false, true);
		$userId = CRTSession::get('userId');

		$sql = "INSERT INTO ".CRTBlock::LogTableName." 
			(userId, blockId, prevNH, newNH, dateModified) VALUES
			($userId, ".$this->oid.", ".$this->db->qstr($this->name1).", ".$this->db->qstr($nhName).", GETDATE())";
		// UNCOMMENT - toby
		//$this->db->_Execute($sql);

		$this->init($this->oid);
	}

	public static function rejectPending($id) {
		$db = DBs::getGISDB();
		$id = Clean::int($id);
		$sql = "DELETE FROM ".PENDING_TBL." WHERE id=$id";
		$db->_Execute($sql);
		
	}

	public static function clearPending($blockId) {
		$db = DBs::getGISDB();
		$blockId = Clean::posInt($blockId);

		$sql = "DELETE FROM ".PENDING_TBL." WHERE blockId=$blockId";
		$db->_Execute($sql);

	}

	// Make sure the colors table is populated. Create if not
	public static function checkColors() {
		$db = DBs::getGISDB();
		$sql = "SELECT count(*) FROM ".COLORS_TBL;
		$count = $db->GetOne($sql);

		if(!$count) {
			$sql = "SELECT DISTINCT name1 FROM ". CRTBlock::TableName;
			$nhs = $db->GetCol($sql);
			foreach($nhs as $n) 
				CRTBlock::addNhColor($n);
		}
	
	}

	public static function getNeighborhoods($extents=null) {
		$db = DBs::getGISDB();

		if($extents) {
			$minX = Clean::float($extents['minX']);
			$maxX = Clean::float($extents['maxX']);
			$minY = Clean::float($extents['minY']);
			$maxY = Clean::float($extents['maxY']);
			$extSql = "WHERE st.intersects(st.transform(GIS_DATA_simple,4326), (st.geomfromtext('POLYGON(($minX $minY, $minX $maxY ,$maxX $maxY ,$maxX $minY ,$minX $minY))',4326))) = 1 AND name1 IS NOT NULL and name1!='' and name1!='Array'";
		}

		$sql = "SELECT DISTINCT name1 FROM ".BLOCKS_TBL." $extSql";
		$nhs = $db->GetCol($sql);
		return $nhs;
	}

	public static function updateBlocks($blocks, $nhName) {
		$db = DBs::getGISDB();
		$blocks = Clean::intArray($blocks);
		$nhName = Clean::string($nhName);
		
		foreach($blocks as $b) {
			$block = new CRTBlock($b);
			$block->logUpdate($nhName);
		}
		
		$sql = "UPDATE ".BLOCKS_TBL." SET name1=".$db->qstr($nhName)." WHERE oid IN (".implode(',', $blocks).")";
                $db->_Execute($sql);
	}

	public static function getPending($blockId) {
		$db = DBs::getGISDB();
		$blockId = Clean::posInt($blockId);
		
		$sql = "SELECT k.name, k.id, u.username 
			FROM ".PENDING_TBL." k
			JOIN crtUsers u
			ON u.id=k.userId
			WHERE k.blockId=$blockId ORDER BY k.name ASC";

		return $db->GetArray($sql);
	}

	public static function addPending($blocks, $nhName) {
		$db = DBs::getGISDB();
		$blocks = Clean::intArray($blocks);
		$nhName = Clean::string($nhName);
		$user = CRTUser::getActiveUser();
				
		foreach($blocks as $block) {
			$sql = "select id from ".PENDING_TBL." WHERE userId={$user->id} and blockId=$block";
			$id = $db->GetOne($sql);
			
			if($id) {
				$sql = "UPDATE ".PENDING_TBL." SET name=".$db->qstr($nhName)." WHERE id=$id";
			} else {
				$sql = "INSERT INTO ".PENDING_TBL." (blockId, userId, name) VALUES ($block, {$user->id}, ".$db->qstr($nhName).")";
			}

			$db->_Execute($sql);
		}
	}

	public static function addNhColor($nhName) {
		$db = DBs::getGISDB();
		$color = sprintf("%02X%02X%02X", mt_rand(0, 255), mt_rand(0, 255), mt_rand(0, 255));
                $sql = "INSERT INTO ".COLORS_TBL." (name, color) VALUES (".$db->qstr($nhName).", ".$db->qstr($color).")";
                $db->_Execute($sql);
	}
	
}


?>
